using System;
using System.Data;
using System.Collections;

using BO = PHSRAG.Insight21.BusinessObjects;
using ICachePolicy = PHSRAG.Insight21.Policy.ICachePolicy;
using PHSRAG.Utility;
using GenerateKey = PHSRAG.Insight21.BusinessObjects.ModuleKey;

namespace PHSRAG.Insight21.CDS
{
	/// <summary>
	/// Summary description for RefCodes.
	/// </summary>
	public class RefCodes : BO.BusinessObject
	{
        public enum RefCodeColumns
        {
            RefDomain,
            RefCode, 
            RefMeaning,
            Active
        }

		#region Constants
		private static string RefCodeCacheKey = GenerateKey.MakeKey("RefCodesCacheKey_82F89EB8-AA99-4eff-90CF-1C5B805BE3CF",GenerateKey.Common);
		private const string GetRefCodesSProcName = "GetRefCodes";
		private const string RefCodeAttributeForeignKeyName = "FK_RefCode_RefCodeAttribute";
		private const string RefCodeTableName = "RefCode";
		private const string RefCodeAttributeTableName = "RefCodeAttribute";
		#endregion Constants

		#region Constructor
		/// <summary>
		/// Instantiates the data container.
		/// </summary>
		/// <param name="cachePolicy">Application's cache policy.</param>
		public RefCodes(ICachePolicy cachePolicy) : base(cachePolicy)
		{
			DataContainer = new DataSet();
		}
		#endregion Constructor

		#region Base Class Overrides
		/// <summary>
		/// Stores this instance using a specified key based on the Cache Policy
		/// </summary>
		public override void Cache()
		{
			CachePolicy.Cache(RefCodeCacheKey, this);
		}

		
		/// <summary>
		/// Removes this instance
		/// </summary>
		public override void UnCache()
		{
			CachePolicy.UnCache(RefCodeCacheKey);
		}

		
		/// <summary>
		/// Loads data from RefCode and RefCodeAttributes into the base container
		/// </summary>
		/// <param name="connectionString">Connection string to the database</param>
		/// <param name="args">No arguments are required</param>
		public override void Load(string connectionString, params object[] args)
		{
			DataContainer = DataAccess.GetDataSet(connectionString, GetRefCodesSProcName, null);
			DataContainer.Tables[0].TableName = RefCodeTableName;
			DataContainer.Tables[1].TableName = RefCodeAttributeTableName;
			//setting up the relation between RefCode and RefCodeAttribute tables in the dataset
			DataContainer.Relations.Add(RefCodeAttributeForeignKeyName, DataContainer.Tables[RefCodeTableName].Columns["RefCode"],
				DataContainer.Tables[RefCodeAttributeTableName].Columns["RefCode"]);
		}

		#endregion Base Class Overrides

		#region Private Methods
		/// <summary>
		/// Builds a RefCode object using the details passed in the DataRow object
		/// </summary>
		/// <param name="refCodeData">RefCode details</param>
		/// <returns>RefCode object</returns>
		private RefCode BuildRefCode(DataRow refCodeData)
		{
			RefCode refCode = null;
			Hashtable refCodeAttributeValue = new Hashtable();
			DataRow[] refCodeAttributesData;
			
			//fetch child rows for the current row and build the attribute/value pair
			refCodeAttributesData = refCodeData.GetChildRows(RefCodeAttributeForeignKeyName);
			for (int j=0; j<refCodeAttributesData.Length; ++j)
			{
				refCodeAttributeValue.Add(refCodeAttributesData[j]["Attribute"].ToString(), 
					refCodeAttributesData[j]["Value"].ToString());
			}

			refCode = new RefCode(CachePolicy, refCodeData["RefDomain"].ToString(), refCodeData["RefCode"].ToString(),
				refCodeData["RefMeaning"].ToString(), refCodeData["Active"].ToString()[0], 
				refCodeAttributeValue);
		
			return refCode;
		}

		#endregion
		
		#region Public Methods
		/// <summary>
		/// Gets a list of RefCode objects with the specified domain
		/// </summary>
		/// <param name="domain">Name of the domain</param>
		/// <returns>List of RefCode objects</returns>
		public ArrayList GetRefCodes(string domain)
		{
			
			RefCode refCode;
			ArrayList refCodesList = null;

			DataRow[] refCodesData = DataContainer.Tables[RefCodeTableName].Select("RefDomain = '" + domain + "'");
			if (refCodesData.Length > 0)
			{
				refCodesList = new ArrayList();
				for (int i=0; i<refCodesData.Length; ++i)
				{
					refCode = BuildRefCode(refCodesData[i]);
					refCodesList.Add(refCode);
				}
			}
			
			return refCodesList;
		}

		
		/// <summary>
		/// Gets a RefCode object with the specified domain and code
		/// </summary>
		/// <param name="code">RefCode value</param>
		/// <returns>RefCode object</returns>
		public RefCode GetRefCode(string code)
		{
			RefCode refCode = null;
			DataRow[] refCodesData = DataContainer.Tables[RefCodeTableName].Select("RefCode = '" + code + "'");
			if (refCodesData.Length > 0)
			{
				refCode = BuildRefCode(refCodesData[0]); //only one RefCode record exists with the specified domain and code
			}
			
			return refCode;
		}

		
		/// <summary>
		/// Gets a RefCode object with the specified domain and code
		/// </summary>
		/// <param name="domain">Name of the RefDomain</param>
		/// <param name="code">RefCode value</param>
		/// <returns>RefCode object</returns>
		public RefCode GetRefCode(string domain, string code)
		{
			RefCode refCode = null;
			DataRow[] refCodesData = DataContainer.Tables[RefCodeTableName].Select("RefDomain = '" + domain + "' AND RefCode = '" + code + "'");
			if (refCodesData.Length > 0)
			{
				refCode = BuildRefCode(refCodesData[0]); //only one RefCode record exists with the specified domain and code
			}
			
			return refCode;
		}
		
		
		/// <summary>
		/// Gets a list of RefCode objects with the specified domain and which have the specified attribute/value pairs
		/// </summary>
		/// <param name="domain">RefDomain value</param>
		/// <param name="attributeName">Name of the Attribute</param>
		/// <param name="attributeValue">Value of the Attribute</param>
		/// <returns>List of RefCode objects</returns>
		public ArrayList GetRefCodes(string domain, string attributeName, string attributeValue)
		{
			DataRow[] refCodeAttributesData;
			RefCode refCode;
			ArrayList refCodesList = null;

			DataRow[] refCodesData = DataContainer.Tables[RefCodeTableName].Select("RefDomain = '" + domain + "'");
			if (refCodesData.Length > 0)
			{
				refCodesList = new ArrayList();
				for (int i=0; i<refCodesData.Length; ++i)
				{
					refCodeAttributesData = refCodesData[i].GetChildRows(RefCodeAttributeForeignKeyName);
					for (int j=0; j<refCodeAttributesData.Length; ++j)
					{
						if (refCodeAttributesData[j]["Attribute"].ToString().CompareTo(attributeName) == 0 &&
							refCodeAttributesData[j]["Value"].ToString().CompareTo(attributeValue) == 0)
						{
							refCode = BuildRefCode(refCodesData[i]);
							refCodesList.Add(refCode);
						}
					}
				}
			}
			return refCodesList;
		}


		/// <summary>
		/// Returns refcodes sorted based on the value for the specified attribute
		/// </summary>
		/// <param name="domain">Domain to which the refcodes belong</param>
		/// <param name="sortAttributeName">Name of the attribute, the value of which is used to sort refcodes</param>
		/// <returns>a sorted collection of refcodes</returns>
		public ArrayList GetSortedRefCodes(string domain, string sortAttributeName)
		{
			ArrayList refCodesList = null;
			
			//fetch Attribute rows with the specified domain and sortAttributeName and order it by value column
			DataView refCodeAttributeView = new DataView(DataContainer.Tables[RefCodeAttributeTableName], "RefDomain = '" + domain + "' and Attribute = '" + sortAttributeName + "'", "Value", DataViewRowState.CurrentRows);
			if (refCodeAttributeView.Count > 0)
			{
				refCodesList = new ArrayList();
				//for each row fetch above get the parent row and build the refcode object
				foreach(DataRowView rowView in refCodeAttributeView)
				{
					DataRow refCodesData = rowView.Row.GetParentRow(RefCodeAttributeForeignKeyName);
					refCodesList.Add(BuildRefCode(refCodesData));
				}
			}

			return refCodesList;
		}

		/// <summary>
        /// Returns refcodes sorted based on the value for the specified attribute
        /// </summary>
        /// <param name="domain">Domain to which the refcodes belong</param>
        /// <param name="sortBy">Column which to sort by</param>
        /// <returns>a sorted collection of refcodes</returns>
        public ArrayList GetSortedRefCodes(string domain, RefCodeColumns sortBy)
        {
            ArrayList refCodesList = null;

            //fetch Attribute rows with the specified domain and sortAttributeName and order it by value column
            DataRow[] refCodesData = DataContainer.Tables[RefCodeTableName].Select("RefDomain = '" + domain + "'", Enum.GetName(typeof(RefCodeColumns), sortBy));
            if (refCodesData.Length > 0)
            {
                refCodesList = new ArrayList();
                for (int i = 0; i < refCodesData.Length; ++i)
                {
                    refCodesList.Add(BuildRefCode(refCodesData[i]));
                }
            }

            return refCodesList;
        }

		/// <summary>
		/// Reloads the data container with data from RefCode and RefCodeAttributes database tables
		/// </summary>
		/// <param name="connectionString">Connection string to the database</param>
		public void Refresh(string connectionString)
		{
			Load(connectionString, null);
		}

      
		/// <summary>
		/// Cache Key
		/// </summary>
		public static string CacheKey
		{
			get
			{
				return RefCodeCacheKey;
			}
		}
		
		#endregion Methods

		#region Static Methods
		/// <summary>
		/// Returns all the refdomains. This method hits the database to fetch all the domains
		/// as the user might, on the fly, add new domains to refcode table and these new domains
		/// will not be available until the iis is reset on the webserver
		/// </summary>
		/// <returns></returns>
		public static DataTable GetDomains(string connectionString)
		{
			try
			{
				return Utility.DataAccess.GetDataTable( connectionString, "GetRefDomains", null);
			}
			catch(Exception ex)
			{
				throw new Exception("Failed to load RefDomains.", ex);
			}
		}
		
		
		/// <summary>
		/// Gets RefCodes with the specified domain
		/// </summary>
		/// <param name="cachePolicy">Prevalent cache policy</param>
		/// <param name="refDomain">RefDomain</param>
		/// <returns>RefCodes with the specified domain</returns>
		public static ArrayList GetRefCodesByDomain(ICachePolicy cachePolicy, string refDomain)
		{
			ArrayList refCodes = new ArrayList();
			try
			{
				DataSet ds = Utility.DataAccess.GetDataSet(Utility.DataAccess.GetConnectionString(), "GetRefCodesByDomain", 
					Utility.DataAccess.BuildSqlParameterArray
					("@refDomain", SqlDbType.VarChar, refDomain));
				if (ds.Tables.Count > 0)
				{
					foreach( DataRow row in ds.Tables[0].Rows )
					{
						RefCode refCode = new RefCode(cachePolicy, row);
						refCodes.Add(refCode );
					}
				}
				else
				{
					throw new Exception("GetRefCodesByDomain stored procedure returned incorrect results");
				}
			}
			catch(Exception ex)
			{
				throw new Exception("Failed to load Refcodes with the specified domain", ex);
			}

			return refCodes;
		}
		#endregion
	}
}
